##############################
# Migrant Care Worker Ratio
##############################
library(tidyverse)
library(ggplot2)
library(forcats)
library(readstata13)
library(RColorBrewer)

# Set your working directory
setwd("~/Dropbox/2001_replication/draft/data")

# Load data
imm_occ <- read.csv("input/ESTA50480_200625_02.csv", stringsAsFactors = FALSE)
forpop <- read.dta13("input/forpop.dta")

# Extract "51: Personal and protective service workers"
imm_service <- imm_occ %>% filter(ISCO2D == "510") %>%
  dplyr::select(COUNTRY, YEAR, NATIONAL, VALUE) %>%
  pivot_wider(names_from = NATIONAL, values_from = VALUE)

# Compute the share of non-EU workers
imm_service <- imm_service %>%
  mutate(total = EU28_NAT + NON_EU28_NAT + `Reporting country`,
         per_noneu = NON_EU28_NAT / total)

# 2002-2014
summary_immserv <- imm_service %>%
  filter(YEAR >= 2002, YEAR <= 2014) %>%
  group_by(COUNTRY, YEAR) %>%
  summarise(serv_noneu = mean(per_noneu)) 

# Compute MCWR
summary_immserv <- summary_immserv %>%
  right_join(forpop, by = c("COUNTRY" = "cntry", "YEAR" = "year")) %>%
  mutate(forpop = forpop/100) %>%
  mutate(mcwr = serv_noneu/forpop) %>%
  dplyr::select(COUNTRY, YEAR, mcwr) 

summary_immserv$YEAR <- as.numeric(summary_immserv$YEAR)

##################
# Figure 1
##################
p_immserv <- summary_immserv %>%
  group_by(COUNTRY) %>%
  summarize(mcwr = mean(mcwr, na.rm=T)) %>%
  na.omit() %>%
  mutate(Welfare = ifelse(COUNTRY %in% c("BE", "DE", "NL", "AT", "CH", "FR"), "Continental",
                          ifelse(COUNTRY %in% c("DK", "SE", "NO", "FI"), "Nordic",
                                 ifelse(COUNTRY %in%  c("IE", "GB"), "Liberal", "Southern")))) %>%
  mutate(COUNTRY = recode(COUNTRY, "ES" = "Spain", "IT" = "Italy", "DE" = "Germany", "DK" = "Denmark",
                          "AT"="Austria", "FI"="Finland", "IE"= "Ireland", "FR"= "France",
                          "NO" = "Norway", "BE"="Belgium", "SE"="Sweden", "NL"="Netherlands"))

p_immserv %>% ggplot(aes(x = reorder(COUNTRY,-mcwr), y=mcwr)) +
  geom_bar(stat="identity", aes(fill = Welfare)) +
  scale_fill_manual(values=c("chocolate4",RColorBrewer::brewer.pal(3,"Dark2"))) +
  xlab("Country") + ylab("Migrant Care Worker Ratio") +
  theme_bw() + theme(legend.position = c(0.9, 0.8))

# Save
summary_immserv$YEAR <- as.character(summary_immserv$YEAR)
summary_immserv$COUNTRY <- as.character(summary_immserv$COUNTRY)
summary_immserv$COUNTRY[summary_immserv$COUNTRY=="UK"] <- "GB"
summary_immserv <- summary_immserv %>% rename("cntry" = "COUNTRY", "year" = "YEAR")

write.csv(summary_immserv, "output/mcwr.csv", row.names = FALSE)

